cauos
asked on
database file shrink error
I have a database on MS SQL server 2005 with 32 GB mdf file and 6 GB ldf file, I have job that shrink the log file every day but now I am trying to shrink the data file after i have removed some big tables used for archieving.
the available space for allocation is around 27 GB but when running the shrink query it gives me an error as following:
the query :
DBCC SHRINKDATABASE(dbName, 1, TRUNCATEONLY)
BACKUP LOG dbName WITH TRUNCATE_ONLY
DBCC SHRINKFILE(dbName_Log, 1)
the error i got:
Msg 3140, Level 16, State 3, Line 1
Could not adjust the space allocation for file dbName_Data'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
and the size of the mdf file still the same 32 GB after this process !!!
could anybody help???
the available space for allocation is around 27 GB but when running the shrink query it gives me an error as following:
the query :
DBCC SHRINKDATABASE(dbName, 1, TRUNCATEONLY)
BACKUP LOG dbName WITH TRUNCATE_ONLY
DBCC SHRINKFILE(dbName_Log, 1)
the error i got:
Msg 3140, Level 16, State 3, Line 1
Could not adjust the space allocation for file dbName_Data'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
and the size of the mdf file still the same 32 GB after this process !!!
could anybody help???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
>I have job that shrink the log file every day
either, you should have the database in single recovery mode, and then you don't need that job
OR
you should have the database in full recovery mode, then have a regular (hourly or even more often) job that runs a transaction log backup, then you don't need the job to shrink.
so:
check the recovery mode of your db, and react accordingly
either, you should have the database in single recovery mode, and then you don't need that job
OR
you should have the database in full recovery mode, then have a regular (hourly or even more often) job that runs a transaction log backup, then you don't need the job to shrink.
so:
check the recovery mode of your db, and react accordingly
ASKER
give me the notification about the prcentage size after shrinking
BACKUP LOG TWO WITH TRUNCATE_ONLY
DBCC SHRINKFILE(TWO_Data, 0,TRUNCATEONLY)
DBCC SHRINKFILE(TWO_Log, 0,TRUNCATEONLY)