?
Solved

database file shrink error

Posted on 2009-12-18
4
Medium Priority
?
1,044 Views
Last Modified: 2012-05-08
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???
0
Comment
Question by:cauos
4 Comments
 
LVL 20

Accepted Solution

by:
Marten Rune earned 1000 total points
ID: 26086136
This command is wrong 'DBCC SHRINKFILE(dbName_Log, 1)' The number 1 should be desired MB the file should be after the schrink operation.
You can use this for both mdf and ldf files.

so use 'DBCC SHRINKFILE(dbName, DesiredSizeForMDFFileInMB)'
and
'DBCC SHRINKFILE(dbName_Log, DesiredSizeForLDFFileInMB)'

//Marten
0
 
LVL 5

Expert Comment

by:rizwanidrees
ID: 26086171
USE TWO
BACKUP LOG TWO WITH TRUNCATE_ONLY
DBCC SHRINKFILE(TWO_Data, 0,TRUNCATEONLY)
DBCC SHRINKFILE(TWO_Log, 0,TRUNCATEONLY)

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26086201
>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
0
 
LVL 4

Author Closing Comment

by:cauos
ID: 31668041
give me the notification about the prcentage size after shrinking
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question