DBCC shrinkdatabase , transaction log shrink

Hi All,
Last night I created sql JOB to shrink the database.
I used the following command
dbcc shrinkdatabase('xxxx')
I scheduled it at 11:00 PM.
Today morning when I saw the job status, it says it failed.
Duration of job run was 2 hours.
In the job history it is saying as below.
Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE 420
00]
(Error 3140) DBCC
execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE
01000] (Error 2528). The step failed.
It puts the following in the eventlog.
SQL Server Scheduled Job 'Shrink xxxx Database'
(0x935A70A0A9B0A643B6336153DD61E128) - Status:
Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The Job
was invoked by
Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This is our production database and it is being logshipped to other server.
My database recovory model set to FULL.
In DB options I unchecked auto shrink option.
I want to shirink database only in night time.
Question-1: Do you guys have any idea why this job failed?
Question-2:Usually what is the permissible value of transactionlog (.ldf)
file size?
Some times to reduce the transaction log file (.LDF) , I am doing as below.
I detach the database and rename the .ldf file name, and attach the database
with only mdf file
,SQL server creates new ldf file with minimum size. delete the old .ldf file
.
Question-3: By doing this , AM I loosing some data/transactions stored in
old .ldf file?
Any kind of help is greatly appreciated.
Thanks
KumarKumar,
Try doing the SHRINKDATABASE manually (not as a job) to rule out the
possible causes for you failure. If this op is just to reduce the size of
the transaction log, try DBCC SHRINKFILE instead of your current process.
Also see:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318/
and
Tibor's page:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:813BD9B6-E8B8-43FA-894B-D2D3A5DF2D1B@.microsoft.com...
> Hi All,
> Last night I created sql JOB to shrink the database.
> I used the following command
> dbcc shrinkdatabase('xxxx')
> I scheduled it at 11:00 PM.
> Today morning when I saw the job status, it says it failed.
> Duration of job run was 2 hours.
> In the job history it is saying as below.
> Could not adjust the space allocation for file 'xxxx_data' [SQLSTATE
> 42000]
> (Error 3140) DBCC
> execution completed. If DBCC printed error messages, contact your system
> administrator. [SQLSTATE
> 01000] (Error 2528). The step failed.
>
> It puts the following in the eventlog.
> SQL Server Scheduled Job 'Shrink xxxx Database'
> (0x935A70A0A9B0A643B6336153DD61E128) - Status:
> Failed - Invoked on: 2005-10-20 23:00:00 - Message: The job failed. The
> Job
> was invoked by
> Schedule 17 (Schedule1). The last step to run was step 1 (Step1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
>
> This is our production database and it is being logshipped to other
> server.
>
> My database recovory model set to FULL.
> In DB options I unchecked auto shrink option.
> I want to shirink database only in night time.
> Question-1: Do you guys have any idea why this job failed?
>
> Question-2:Usually what is the permissible value of transactionlog (.ldf)
> file size?
> Some times to reduce the transaction log file (.LDF) , I am doing as
> below.
> I detach the database and rename the .ldf file name, and attach the
> database
> with only mdf file
> ,SQL server creates new ldf file with minimum size. delete the old .ldf
> file.
>
> Question-3: By doing this , AM I loosing some data/transactions stored in
> old .ldf file?
>
> Any kind of help is greatly appreciated.
> Thanks
> Kumar
>
>
>
>